﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
//1-26-2012
// Will replace web services which are not supported without IIS extensions download..
//2-18-2012 NOTE: we want to search by subspecialty as well. stupid if you ask me 
//But....So ignore if none is given of course..
namespace SBS_webservicesDocMain
{
    public partial class SBS_fetchDocs : System.Web.UI.Page
    {
        private SqlDataAdapter m_DataAdapter = null;
        
        ///NOTE: Change from DEV to Main table on Main Site please..
        string remoteConnectDev = @"Data Source=74.54.54.98;User ID=SBSUSER;Password=DocDoc;Trusted_Connection=false;Initial Catalog=dbhaelthcare-dev";
        //string remoteConnect = @"Data Source=74.54.54.98;User ID=sbsuser;Password=DocDoc;Trusted_Connection=false;Initial Catalog=dbhealthcare";
        string remoteConnect = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
       
        private string lname = "";
        private string specialty = "";
        private string subspecialty = "";
        private string city = "";
        private string state = "";
        private string zipcodeforsearch = "";
        private string srating = "0.00";
        private double dRating = 0.00;
        private bool ignoreCity = true;
        private bool ignoreLN = true;
        private bool ignoreZip = true;
        private bool ignorestate = true;
        private bool ignorerating = true;
        //NEED PARAMS://this.m_UserName = base.Request.QueryString["UserName"].ToString();
        public void ErrorMsg(string msg, XmlTextWriter writer)
        {
            writer.WriteStartElement("item");
            writer.WriteElementString("ERROR", msg);
            writer.WriteEndElement();
            writer.Flush();
            writer.Close();
            base.Response.End();
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            //SetTes();
            XMLQuery();                        
        }

       
        //2-5-2012. NOTE fellowship is always null. This is not good man..
        //2-16-2012 added insurance, fellowship
        private void XMLQuery()
        {
            XmlTextWriter writer = new XmlTextWriter(base.Response.OutputStream, Encoding.UTF8);
            try
            {
                bool bErrors = false;
                writer.WriteStartDocument();
                writer.WriteStartElement("rss");
                writer.WriteAttributeString("version", "2.0");
                writer.WriteStartElement("channel");
                writer.WriteElementString("title", "Find A Doc Web Services for smart devices or anything else");
                writer.WriteElementString("link", "http://www.finadoc.com");
                writer.WriteElementString("description", "Find A Doc WEB Service developed by www.southcoastsbs.com");
                //writer.WriteRaw("<br/>");
                if (base.Request.QueryString["LName"] != null)
                {
                    this.lname = base.Request.QueryString["LName"].ToString();
                }
                else
                {
                    if (this.ignoreLN == false)
                    {
                        bErrors = true;
                        ErrorMsg("No last name specified. LName=Donovan", writer);
                    }
                }
                //specialty
                if (base.Request.QueryString["Specialty"] != null)
                {                    
                    this.specialty = base.Request.QueryString["Specialty"].ToString();
                }                 
                else
                {
                    bErrors = true;
                    ErrorMsg("No specialty given. Specialty=12", writer);
                }
                //sub specialty 2-18-2012 mod
                if (base.Request.QueryString["SubSpecialty"] != null)
                {
                    this.subspecialty = base.Request.QueryString["SubSpecialty"].ToString();
                }
                //no error
                if (base.Request.QueryString["City"] != null)
                {
                 
                    this.city = base.Request.QueryString["City"].ToString();                 
                }
                else
                {
                    if (ignoreCity == false)
                    {
                        bErrors = true;
                        ErrorMsg("No City given. City=Boston", writer);
                    }
                }
                if (base.Request.QueryString["State"] != null)
                {
                    //this.m_UserName = base.Request.QueryString["UserName"].ToString();
                    this.state = base.Request.QueryString["State"].ToString();
                }
                else
                {
                    if (this.ignorestate == false)
                    {
                        bErrors = true;
                        ErrorMsg("No State given. State=MA", writer);
                    }
                }
                //zip
                if (base.Request.QueryString["Zip"] != null)
                {
                    //this.m_UserName = base.Request.QueryString["UserName"].ToString();
                    this.zipcodeforsearch = base.Request.QueryString["Zip"].ToString();
                }
                else
                {
                    if (this.ignoreZip == false)
                    {
                        bErrors = true;
                        ErrorMsg("No zip code given. Zip=02127", writer);
                    }

                }
                //rating
                if (base.Request.QueryString["Rating"] != null)
                {
                    //this.m_UserName = base.Request.QueryString["UserName"].ToString();
                    this.srating = base.Request.QueryString["Rating"].ToString();
                    try
                    {
                        dRating = Double.Parse(srating);
                    }
                    catch (Exception x)
                    {
                        //ignore
                        dRating = 0.00;
                    }
                }
                else
                {
                    if ( ignorerating == false)
                    {
                        bErrors = true;
                        ErrorMsg("No rating givne. Ex is Rating=70.00. This fetches 70 and greater.", writer);
                    }
                }
                
                if (bErrors == false)
                {
                    //  DONE validation 1-27-2012
                    //

                    System.Collections.ArrayList values = new System.Collections.ArrayList();
                    string sql = " Select D.Id,D.FName,D.MName,D.LName,D.Address1,D.Address2,";
                    sql += "D.Phone,D.City,D.State,D.zipcodeforsearch,";
                    sql += "D.MedicalSchool,D.SpecialityID,";
                    sql += "D.InsuranceIds,D.ResidencyTraining,D.AcceptNew,";
                    sql += "D.GroupPractice, D.PrimarySpecialityID,D.Languages,";
                    sql += "D.InsuranceIds,D.Fellowship,D.Cost, D.Abms_CertificationID,";
                    sql += "D.HospitalName,";
                    //2-5-2102 mods
                    //sql += " D.InsuranceIds, ";//D.Fellowship,D.HospitalName,";
                    //2-5-2012 mods end
                    sql += " sp.Name,sp2nd.Name, ";
                    sql += " md.medicalschool,";
                    sql += "aRating.OverAllRating, ";
                    //hosp aff
                    sql += "act.Name, hp.HName, res.HName ";
                    sql += " From  tbl_doctors D ";
                    sql += " left outer join tbl_speciality sp on sp.id=D.specialityid";
                    sql += " left outer join tbl_Secondaryspeciality sp2nd on sp2nd.id=D.PrimarySpecialityID";
                    sql += " left outer join tbl_MedicalSchool md on md.id = D.MedicalSchool ";
                    //---WRONGsql += " left outer join tbl_MedicalSchool mr on mr.id = D.ResidencyTraining ";                    
                    sql += " left outer join tbl_docOverAllRating aRating on aRating.Dr_id=D.Id ";
                    sql += " left outer join tbl_MajorActivity act on act.id=D.MajorActivityID ";
                    //fellowship
                    sql += " left outer join tbl_hospitals hp on hp.Id=D.Fellowship ";
                    sql += " left outer join tbl_hospitals res on res.Id = D.ResidencyTraining ";
                    ////--- 3-18-2012-mod sql += " WHERE D.SpecialityID={0} ";
                    sql += " WHERE  ( D.SpecialityID=" + specialty + ") ";
                    //2ndary
                    if ( !this.subspecialty.Equals(""))
                    {
                        //--- 3-18-2012-mod sql += " AND D.PrimarySpecialityID={6} ";
                        sql += " AND  (D.PrimarySpecialityID=" + subspecialty + " ";
                    }
                    //last name
                    if ( ! lname.Equals(""))
                        sql += " AND ( D.LName like '" + lname + "%' ) ";
                        //--- 3-18-2012-modsql += " AND ( D.LName like '{1}%' ) ";
                    //required
                    ////--- 3-18-2012-mod sql += " AND( D.City like '{2}%' )";
                    if ( ! city.Equals(""))
                        sql += " AND( D.City like '" + city + "%' )";
                    if ( ! state.Equals(""))
                        sql += " AND (D.State='" + state + "' ) ";
                        ////--- 3-18-2012-modsql += " AND (D.State='{3}' ) ";
                    if ( ! zipcodeforsearch.Equals(""))
                        sql += " AND (D.zipcodeforsearch='" + zipcodeforsearch + "' ) ";
                        //--- 3-18-2012 modsql += " AND (D.zipcodeforsearch='{4}' ) ";
                    //rating

                    ////--- 3-18-2012-modsql += " AND( aRating.OverallRating >={5} )";
                    if (dRating != 0.00 )
                        sql += " AND( aRating.OverallRating >=" + dRating + " ) ";
                    
                    //always do this sort
                    sql += " Order by D.LName ";
                    ////--- 3-18-2012-mod
                    /*values.Add(specialty);
                    values.Add(lname);
                    values.Add(city);
                    values.Add(state);
                    values.Add(zipcodeforsearch);
                    values.Add(dRating);*/
                    //6th arg zero based meaning 7 in actuality subspecialty
                    
                    //reset same var. yes
                    //--WE ARE redoing the logic completley. SO leave this old code in until further notice. THis technique
                    //will not work due to the fact that there might be all kinds of args
                    //--- 3-18-2012-modsql = String.Format(sql, values.ToArray());
                    //debug arg
                    if (base.Request.QueryString["SQLDEBUG"] != null)
                    {
                        string[] sqs = sql.Split(',');
                        string sq = "";
                        for (int i = 0; i < sqs.Length; i++)
                        {
                            sq += sqs[i] + "," + "\r\n";
                        }
                        writer.WriteElementString("SQL", sq);
                    }
                    
                    System.Console.WriteLine("sQL = " + sql);
                    m_DataAdapter = new SqlDataAdapter(sql, remoteConnect);
                    DataSet ds = new DataSet();
                    m_DataAdapter.Fill(ds, "tbl_doctors");
                    DataTable dt = ds.Tables[0];
                    if (dt == null || dt.Rows.Count==0)
                    {
                        writer.WriteRaw("\n"); writer.WriteRaw("\n");
                        writer.WriteStartElement("item");
                        writer.WriteElementString("NORECORDS", "Zero Results");
                        writer.WriteEndElement();
                    }
                    //end header XML
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow drow = dt.Rows[i];
                        writer.WriteStartElement("item");
                        writer.WriteRaw("\n"); writer.WriteRaw("\n");
                        string ID = drow["Id"].ToString();
                        writer.WriteElementString("Id", ID);
                        writer.WriteRaw("\n");
                        //full name
                        //string FName = drow["FName"].ToString();                        
                        //string MName = drow["MName"].ToString();                        
                        //string LName = drow["LName"].ToString();                        
                        //string fullName = FName + " " + MName.Trim() + " " + LName;
                        //writer.WriteElementString("Name", fullName);
                        //writer.WriteRaw("\n");
                        writer.WriteElementString("title", drow["FName"].ToString() + drow["MName"].ToString() + drow["LName"].ToString());
                        writer.WriteRaw("\n");
                        writer.WriteElementString("link", "http://www.finadoc.com");
                        writer.WriteRaw("\n");
                        string Address1 = drow["Address1"].ToString();
                        writer.WriteElementString("Address1", Address1);
                        writer.WriteRaw("\n");
                        string City = drow["City"].ToString();
                        writer.WriteElementString("City", City);
                        writer.WriteRaw("\n");
                        string st = drow["State"].ToString();
                        writer.WriteElementString("state", st);
                        writer.WriteRaw("\n");
                        //zip. OPtional maybe
                        string zp = drow["zipcodeforsearch"].ToString();
                        writer.WriteElementString("Zipcode", zp);
                        writer.WriteRaw("\n");
                        /*string zip = drow["zipcodeforsearch"].ToString();
                        writer.WriteElementString("zipcodeforsearch", zip);
                        writer.WriteRaw("\n");*/
                        //Specialty or PRIMARY. 2 things to note:1 it's mispelled and two, the
                        string specialtymain = drow["Name"].ToString();//note douche spelling wrong..                    
                        writer.WriteElementString("PrimarySpeciality", specialtymain);
                        writer.WriteRaw("\n");
                        // 2ndary specialtyinstead 2-5-2012
                        string secondaryspecialty = drow["Name1"].ToString();//note douche spelling wrong..                    
                        writer.WriteElementString("Secondarypeciality", secondaryspecialty);
                        writer.WriteRaw("\n");
                        string medschool1 = drow["medicalschool1"].ToString();
                        writer.WriteElementString("medicalschool", medschool1);
                        writer.WriteRaw("\n");

                        string residency = drow["HName1"].ToString();
                        writer.WriteElementString("ResidencyTraining", residency);
                        writer.WriteRaw("\n");
                        //strange. Null or 1 means yes, 0 means no
                        string acceptnew = drow["AcceptNew"].ToString();
                        if (acceptnew.Equals("")||acceptnew.Equals("1"))
                            acceptnew = "1";
                        else //either 1,0 or null
                            acceptnew = "0";
                        writer.WriteElementString("AcceptNew", acceptnew);
                        writer.WriteRaw("\n");
                        string group = drow["GroupPractice"].ToString();
                        writer.WriteElementString("GroupPractice", group);
                        writer.WriteRaw("\n");
                        //activity name
                        string activityName = drow["Name2"].ToString();//bad naming of fields..
                        writer.WriteElementString("Activity", activityName);
                        writer.WriteRaw("\n");
                        //languages
                        string languages = drow["Languages"].ToString();//bad naming of fields..
                        writer.WriteElementString("Languages", languages);
                        writer.WriteRaw("\n");
                        //end activity
                        string OverAllRating = drow["OverAllRating"].ToString();
                        writer.WriteElementString("OverAllRating", OverAllRating);
                        writer.WriteRaw("\n");
                        //2-16-2012 fudge for now
                        string subRating = OverAllRating;
                        writer.WriteElementString("SubRating", subRating);
                        writer.WriteRaw("\n");
                        //insurance 2-16-2012
                        string ins = drow["InsuranceIds"].ToString();
                        if (ins.Equals(""))
                            ins = "0";
                        else
                            ins = "1";//meaning filled in of course = yes
                        
                        writer.WriteElementString("Insurance", ins);
                        writer.WriteRaw("\n");
                        //2-16-2012 cost
                        string cost = drow["Cost"].ToString();
                        writer.WriteElementString("Cost", cost);
                        writer.WriteRaw("\n");
                        //board 2-16-2012
                        string board = drow["Abms_CertificationID"].ToString();
                        if (board.Equals("True"))
                            board = "1";
                        else
                            board = "0";
                        writer.WriteElementString("Board", board);
                        writer.WriteRaw("\n");
                        //fellowship confusing string  in word doc, but screenshots say yes
                        string fellow = drow["HName"].ToString();//bad naming of fields..                        
                        writer.WriteElementString("Fellowship", fellow);
                        writer.WriteRaw("\n");
                        //hospital name? Affiliation? not sure
                        string hospAff = drow["HospitalName"].ToString();
                        writer.WriteElementString("HospAffl", hospAff);
                        writer.WriteRaw("\n");
                        //cols
                        bool bDebug = false;
                        if (bDebug == true)
                        {
                            for (int x = 0; x < dt.Columns.Count; x++)
                            {
                                DataColumn dc = dt.Columns[x];
                                string name = dc.ColumnName;
                                writer.WriteElementString("ColumnName", name);
                                writer.WriteRaw("\n");
                            }
                        }
                        //insurance
                        /*string ins = drow["InsuranceIds"].ToString();//bad naming of fields..
                        writer.WriteElementString("Insurance", ins);
                        writer.WriteRaw("\n");*/
                        //insurance end
                        //he's a jolly good fellow
                        /*string fellow = drow["Fellowship"].ToString();//bad naming of fields..
                        writer.WriteElementString("Fellowship", fellow);
                        writer.WriteRaw("\n");*/
                        //fellowship end
                        //HospAffl
                        /*string hospaff = drow["HospitalName"].ToString();//bad naming of fields..
                        writer.WriteElementString("HospAffl", hospaff);
                        writer.WriteRaw("\n");*/
                        //HospAffl  END
                        writer.WriteEndElement();
                        //writer.WriteRaw("\n");
                    }
                    writer.WriteEndDocument();
                    writer.Flush();
                    writer.Close();
                    base.Response.End();
                }//end if no errors
            }
            catch (Exception x)
            {
                writer.WriteElementString("ERROR", "Catch exception:" + x.Message);
                writer.WriteEndDocument();
                writer.Flush();
                writer.Close();
                base.Response.End();
            }
        }
    }
}
